#
#用于修正允许零库存和负库存出货早期程序bug带来的问题数据
#零库存商品直接出货，除了库存数量外成本和价格数据斗没有
#
#需要在.bahs_profile设置环境变量
#export PSI_PASSWORD=xxxxxxx
#export RDS_AGENT=yyyy
#
#这个部分建立了模型，但未实际使用，因为无法解决多成本的问题
#
#手工在命令行运行 ruby fix_inventory_detail.rb
#
require 'mysql2'

    rds = Mysql2::Client.new(:host => ENV['RDS_AGENT'], :username => "psi_root", :port => '1401', :password => ENV['PSI_PASSWORD'])

    goodsName = {};
    goodsPrice = {};
    goodsCategory = {};
    sql = "select * from psi.t_goods"
    res = rds.query(sql)
    res.each do |res|
        id = res['id'];
        name = res['name'];
        purchase_price = res['purchase_price'];
        category_id = res['category_id'];
        goodsName.store(id,name);
        goodsPrice.store(id,purchase_price);
        goodsCategory.store(id,category_id);
    end
    #goodsName.each { |id,name| puts "#{id} #{name}" }


    #在收发明细表上曾有零出库或入库成本单价，零入库或出库数量的商品列表
    questionGoods = {};
	sql = "select distinct goods_id from psi.t_inventory_detail where 
		( balance_count < 0 or balance_price = 0 or in_count = 0 or out_count <= 0 or in_price <=0 or out_price <=0 )
		 and balance_money >= 0 and warehouse_id='7E6C8D0D-E25A-11E8-8B92-6C92BF475963'
	"
    res = rds.query(sql)
    res.each do |res|
        gid = res['goods_id'];
        price = goodsPrice[gid];
        questionGoods.store(gid,price);
    end
    puts "===questioned goods total: #{questionGoods.count}"
    #questionGoods.each { |gid, price| puts "#{goodsName[gid]} #{gid} #{sprintf("%.2f", price)}" }

    tflag = 0;
    questionGoods.each { |gid, price| 

        ##tag questioned record ONLY ONCE
        #sqlu = "update psi.t_inventory_detail set fixed='tofix' where warehouse_id='7E6C8D0D-E25A-11E8-8B92-6C92BF475963' and goods_id='#{gid}'"
        #resu = rds.query(sqlu)

        #to observer examples
        if (tflag==22) then

            puts "example goods_id: #{gid}"
            sql2 = "select * from psi.t_inventory_detail where warehouse_id='7E6C8D0D-E25A-11E8-8B92-6C92BF475963' and goods_id='#{gid}' order by id desc"
            res2 = rds.query(sql2)

            settle_flag=true

            res2.each do |r2|

                balance_count = 0
                balance_money = 0
                goods_price = goodsPrice[gid]
                inv_detail_id = r2['id']

                sqlin = "select sum(in_count) as in_count_sum from psi.t_inventory_detail where warehouse_id='7E6C8D0D-E25A-11E8-8B92-6C92BF475963' and goods_id='#{gid}' and in_count is not null and fixed != 'fixed'"
                resin = rds.query(sqlin)
                in_count_sum = resin.first['in_count_sum']==nil ? 0 : resin.first['in_count_sum']
                #puts "  in_count_sum: #{sprintf('%.2f',in_count_sum)} "

                sqlout = "select sum(out_count) as out_count_sum from psi.t_inventory_detail where warehouse_id='7E6C8D0D-E25A-11E8-8B92-6C92BF475963' and goods_id='#{gid}' and out_count is not null and fixed != 'fixed'"
                resout = rds.query(sqlout)
                out_count_sum = resout.first['out_count_sum']==nil ? 0 : resout.first['out_count_sum']
                #puts "  out_count_sum: #{sprintf('%.2f',out_count_sum)}"

                balance_count = in_count_sum - out_count_sum
                balance_money = balance_count * goods_price

                if r2['in_count'].nil? then

                    #出库记录
                    #puts "OUT<<<  #{r2['id']} #{sprintf('%.2f',r2['balance_count'])} #{r2['balance_price']} #{r2['balance_money']} | #{sprintf('%.2f',r2['out_count'])} #{r2['out_price']} #{r2['out_money']} #{goodsName[r2['goods_id']]} #{sprintf('%.2f',goodsPrice[r2['goods_id']])} "
                
                    detail_out_count = r2['out_count']
                    detail_out_money = detail_out_count * goods_price

                    sqlu = "update psi.t_inventory_detail set balance_count=#{sprintf('%.3f', balance_count)}, balance_money=#{sprintf('%.3f', balance_money)}, balance_price=#{sprintf('%.3f', goods_price)}, out_money=#{sprintf('%.3f', detail_out_money)}, out_price=#{sprintf('%.3f', goods_price)}, fixed='fixed' where id='#{inv_detail_id}'"

                    puts sqlu

                elsif r2['out_count'].nil? then

                    #入库记录
                    #puts "IN >>>  #{r2['id']} #{sprintf('%.2f',r2['balance_count'])} #{r2['balance_price']} #{r2['balance_money']} | #{sprintf('%.2f',r2['in_count'])} #{r2['in_price']} #{r2['in_money']} #{goodsName[r2['goods_id']]} #{sprintf('%.2f',price)} " 

                    detail_in_count = r2['in_count']
                    detail_in_money = detail_in_count * goods_price

                    sqlu = "update psi.t_inventory_detail set balance_count=#{sprintf('%.3f', balance_count)}, balance_money=#{sprintf('%.3f', balance_money)}, balance_price=#{sprintf('%.3f', goods_price)}, in_money=#{sprintf('%.3f', detail_in_money)}, in_price=#{sprintf('%.3f', goods_price)}, fixed='fixed' where id='#{inv_detail_id}'"

                    puts sqlu


                else
                    puts "Something wrong"
                end

                if settle_flag then
                    puts "更新总帐余额"
                    settle_flag = false
                end

            end

        end
        tflag+=1;

    }

rds.close
